package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.repositories.CrmHistorySQLRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/11/25.
 */
@Repository
@Transactional//(readOnly = true)
public class CrmHistorySQLRepositoryImpl extends BaseRepository implements CrmHistorySQLRepository {
    private Logger log = LoggerFactory.getLogger(CrmHistorySQLRepositoryImpl.class);

    @Override
    public List findTopicLogAboutGroupByPermit(String[] array, String startDate, String endDate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.id, if(a.permit = 'LSH', 'DCH', a.permit) permit, a.version, a.content, a.updateSendInfoDate, a.updateAccount, a.updateTime from crm_topic_log a ");
        if (array != null && array.length > 0) {
            String s = "";
            for (int i = 0; i < array.length; i++) {
                s = s + ":s" + i;
                if (i < array.length - 1) {
                    s = s + ",";
                }
            }
            sb.append(" join ( ");
            sb.append(" select * from lsh经销商表 a where 1 = 1 ");
            sb.append(" and ( a.大区 in (  ");
            sb.append(s);
            sb.append(" ) ");
            sb.append(" or a.小区 in ( ");
            sb.append(s);
            sb.append(" ) ");
            sb.append(" or a.CTA in ( ");
            sb.append(s);
            sb.append(" ) ");
            sb.append(" or a.经销商名 in ( ");
            sb.append(s);
            sb.append(" )) ");
            sb.append(" ) b on a.permit = b.许可_BMBS ");
        }
        sb.append(" where 1 = 1 ");
        if (startDate != null && !"".equals(startDate)) {
            sb.append(" and DATE_FORMAT(a.updateTime,'%Y-%m-%d') >= :startDate ");
        }
        if (endDate != null && !"".equals(endDate)) {
            sb.append(" and DATE_FORMAT(a.updateTime,'%Y-%m-%d') <= :endDate ");
        }
        sb.append(" order by a.updateTime desc ");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map map = new HashMap();
        if (array != null && array.length > 0) {
            for (int j = 0; j < array.length; j++) {
                map.put("s" + j, array[j]);
            }
        }
        if (startDate != null && !"".equals(startDate)) {
            try {
                map.put("startDate", new Date(sdf.parse(startDate).getTime()));
            } catch (ParseException e) {
                log.error("题库日志导出时传入的开始日期格式异常", e);
            }
        }
        if (endDate != null && !"".equals(endDate)) {
            try {
                map.put("endDate", new Date(sdf.parse(endDate).getTime()));
            } catch (ParseException e) {
                log.error("题库日志导出时传入的结束日期格式异常", e);
            }
        }
        return this.getList(sb.toString(), map);
    }
}
